import pandas as pd
import pymysql.cursors
import numpy as np
from confi import CITY_DICT

class ScenicDataAnalyzer:
    """景区游客数据分析工具"""
    
    def __init__(self):
        # 数据库连接配置
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='root',
            database='scenic',
            port=3306,
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor
        )
    
    def fetch_and_analyze_data(self):
        """获取景区数据并进行异常分析"""
        try:
            # 从数据库获取数据
            df = self._fetch_data_from_db()
            
            # 数据处理与分析
            if not df.empty:
                df_processed = self._process_data(df)
                current_month = pd.to_datetime('2024-12-01')
                df_analysis = self._calculate_z_scores(df_processed, current_month)
                
                # 输出异常结果
                self._print_anomalies(df_analysis)
            else:
                print("未获取到有效数据")
                
        except Exception as e:
            print(f"分析过程出错: {e}")
        finally:
            # 关闭数据库连接
            self.conn.close()
    
    def _fetch_data_from_db(self):
        """从数据库获取景区游客数据"""
        with self.conn.cursor() as cursor:
            sql = """
            SELECT 
                LEFT(u.id_no, 4) as city_code, 
                DATE_FORMAT(o.create_time, '%Y-%m') as month, 
                COUNT(u.id) as visitor_count
            FROM ticket_order_user_rel u 
            JOIN ticket_order o ON o.id = u.order_id 
            WHERE LENGTH(u.id_no) = 18 
                AND o.pay_time IS NOT NULL 
                AND o.pay_time != '' 
            GROUP BY city_code, month
            """
            cursor.execute(sql)
            results = cursor.fetchall()
        
        # 转换为DataFrame并过滤无效城市代码
        valid_data = []
        for item in results:
            if item['city_code'] in CITY_DICT:
                valid_data.append({
                    'city_code': item['city_code'],
                    'city_name': CITY_DICT[item['city_code']],  # 使用城市名称映射
                    'month': item['month'],
                    'visitor_count': item['visitor_count']
                })
        
        return pd.DataFrame(valid_data)
    
    def _process_data(self, df):
        """处理数据，转换日期格式"""
        df['month'] = pd.to_datetime(df['month'] + '-01')
        return df
    
    def _calculate_z_scores(self, df, current_month, window_size=6):
        """计算Z-score进行异常检测"""
        # 当前月份数据
        df_current = df[df['month'] == current_month].copy()
        
        # 历史数据 (前6个月)
        history_start = current_month - pd.DateOffset(months=window_size)
        df_history = df[(df['month'] > history_start) & (df['month'] < current_month)]
        
        # 计算历史均值和标准差
        df_baseline = df_history.groupby('city_name')['visitor_count'].agg(['mean', 'std']).reset_index()
        df_baseline.rename(columns={'mean': 'hist_mean', 'std': 'hist_std'}, inplace=True)
        
        # 合并数据
        df_merged = df_current.merge(df_baseline, on='city_name', how='left')
        
        # 计算Z-score
        df_merged['z_score'] = (df_merged['visitor_count'] - df_merged['hist_mean']) / df_merged['hist_std'].replace(0, np.nan)
        
        return df_merged
    
    def _print_anomalies(self, df):
        """打印异常结果"""
        # 游客暴增的城市 (Z-score > 3)
        df_increased = df[df['z_score'] > 3]
        
        # 游客暴跌的城市 (Z-score < -3)
        df_reduced = df[df['z_score'] < -3]
        
        print("游客暴增的城市:")
        if not df_increased.empty:
            print(df_increased[['city_name', 'visitor_count', 'hist_mean', 'z_score']])
        else:
            print("无")
        
        print("\n游客暴跌的城市:")
        if not df_reduced.empty:
            print(df_reduced[['city_name', 'visitor_count', 'hist_mean', 'z_score']])
        else:
            print("无")

if __name__ == '__main__':
    # 创建数据分析器实例并执行分析
    analyzer = ScenicDataAnalyzer()
    analyzer.fetch_and_analyze_data()